package com.daoliuhe.scaffold.tools;

import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.hssf.usermodel.HSSFDataFormatter;
import org.apache.poi.hssf.usermodel.HSSFFont;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.openxml4j.exceptions.InvalidFormatException;
import org.apache.poi.ss.usermodel.*;

import java.io.IOException;
import java.io.InputStream;
import java.util.List;
import java.util.Map;

public class ExcelUtil {

    /**
     * 根据模板导出
     *
     * @param
     * @param list
     * @param beginRowIndex
     * @return
     * @throws IOException
     * @throws InvalidFormatException
     */
    public static Workbook genByTemplete(InputStream is, List<String[]> list, int beginRowIndex) throws InvalidFormatException, IOException {
        Workbook workbook = WorkbookFactory.create(is);
        if (!list.isEmpty()) {
            // 设置样式
            CellStyle cellStyle = workbook.createCellStyle();
            cellStyle.setAlignment(CellStyle.ALIGN_CENTER);
            cellStyle.setVerticalAlignment(CellStyle.VERTICAL_CENTER);
            cellStyle.setWrapText(true); //自动换行

            Sheet sheet = workbook.getSheetAt(0);
            // 标题行占 beginRowIndex 行
            for (String[] v : list) {
                Row row = sheet.createRow(beginRowIndex++);
                for (int i = 0; i < v.length; i++) {
                    Cell cell = row.createCell(i);
                    String str = v[i];
                    cell.setCellValue(str);
                }
            }
        }
        return workbook;
    }

    /**
     * 根据数据导出excel
     * list的第一个元素为标题列
     *
     * @param list
     * @return
     * @throws IOException
     */
    public static Workbook createWorkbook(List<String[]> list) throws IOException {
        Workbook workbook = new HSSFWorkbook();

        if (!list.isEmpty()) {
            // 设置样式
            CellStyle cellStyle = workbook.createCellStyle();
            CellStyle titleStyle = workbook.createCellStyle();

            Font font = workbook.createFont();
            font.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);//粗体显示
            titleStyle.setFont(font);

            cellStyle.setAlignment(CellStyle.ALIGN_LEFT);
            cellStyle.setVerticalAlignment(CellStyle.VERTICAL_CENTER);
            cellStyle.setWrapText(true); //自动换行

            Sheet sheet = workbook.createSheet("导出结果");
            sheet.setDefaultRowHeight((short) 300);
            Row titleRow = sheet.createRow(0);


            Cell cell;
            String[] title = list.get(0);
            for (int i = 0; i < title.length; i++) {
                sheet.setDefaultColumnStyle(i, cellStyle);
                sheet.setColumnWidth((short) i, 6000);
                cell = titleRow.createCell(i);
                cell.setCellValue(title[i]);
                cell.setCellStyle(titleStyle);
            }

            // 标题行占一行
            for (int i = 1; i < list.size(); i++) {
                Row row = sheet.createRow(i);
                String[] v = list.get(i);
                for (int j = 0; j < v.length; j++) {
                    cell = row.createCell(j);
                    String str = v[j];
                    cell.setCellValue(str);
                }
            }
        }
        return workbook;
    }


    /**
     * 根据数据导出excel
     * key: sheet的名称
     * value: 数据的list
     * list的第一个元素为标题列
     *
     * @param map
     * @return
     * @throws IOException
     */
    public static Workbook createWorkbook(Map<String, List<String[]>> map) throws IOException {
        Workbook workbook = new HSSFWorkbook();

        for (Map.Entry<String, List<String[]>> entry : map.entrySet()) {
            String sheetName = entry.getKey();
            List<String[]> list = entry.getValue();
            if (!list.isEmpty()) {
                // 设置样式
                CellStyle titleStyle = workbook.createCellStyle();
                Font font = workbook.createFont();
                font.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);//粗体显示
                titleStyle.setFont(font);


                CellStyle cellStyle = workbook.createCellStyle();
                cellStyle.setAlignment(CellStyle.ALIGN_CENTER);
                cellStyle.setVerticalAlignment(CellStyle.VERTICAL_CENTER);
                cellStyle.setWrapText(true); //自动换行

                Sheet sheet = workbook.createSheet(sheetName);
                sheet.setDefaultRowHeight((short) 300);
                Row titleRow = sheet.createRow(0);

                Cell cell;
                String[] title = list.get(0);
                for (int i = 0; i < title.length; i++) {
                    sheet.setDefaultColumnStyle(i, cellStyle);

                    sheet.setColumnWidth((short) i, 6000);
                    cell = titleRow.createCell(i);
                    cell.setCellValue(title[i]);
                    cell.setCellStyle(titleStyle);
                }

                // 标题行占一行
                for (int i = 1; i < list.size(); i++) {
                    Row row = sheet.createRow(i);
                    String[] v = list.get(i);
                    for (int j = 0; j < v.length; j++) {
                        cell = row.createCell(j);
                        String str = v[j];
                        cell.setCellValue(str);
                    }
                }
            }
        }

        return workbook;
    }


    /**
     * 根据数据导出excel ,封装一层list，用来sheet的排序
     *
     * @param mapList
     * @return
     * @throws IOException
     */
    public static Workbook createWorkbookCustom(List<Map<String, List<String[]>>> mapList) throws IOException {
        Workbook workbook = new HSSFWorkbook();

        if (!mapList.isEmpty()) {
            for (Map<String, List<String[]>> map : mapList) {
                for (Map.Entry<String, List<String[]>> entry : map.entrySet()) {
                    String sheetName = entry.getKey();
                    List<String[]> list = entry.getValue();
                    if (!list.isEmpty()) {
                        // 设置样式
                        CellStyle titleStyle = workbook.createCellStyle();
                        Font font = workbook.createFont();
                        font.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);//粗体显示
                        titleStyle.setFont(font);


                        CellStyle cellStyle = workbook.createCellStyle();
                        cellStyle.setAlignment(CellStyle.ALIGN_CENTER);
                        cellStyle.setVerticalAlignment(CellStyle.VERTICAL_CENTER);
                        cellStyle.setWrapText(true); //自动换行

                        Sheet sheet = workbook.createSheet(sheetName);
                        sheet.setDefaultRowHeight((short) 300);
                        Row titleRow = sheet.createRow(0);

                        Cell cell;
                        String[] title = list.get(0);
                        for (int i = 0; i < title.length; i++) {
                            sheet.setDefaultColumnStyle(i, cellStyle);

                            sheet.setColumnWidth((short) i, 6000);
                            cell = titleRow.createCell(i);
                            cell.setCellValue(title[i]);
                            cell.setCellStyle(titleStyle);
                        }

                        // 标题行占一行
                        for (int i = 1; i < list.size(); i++) {
                            Row row = sheet.createRow(i);
                            String[] v = list.get(i);
                            for (int j = 0; j < v.length; j++) {
                                cell = row.createCell(j);
                                String str = v[j];
                                cell.setCellValue(str);
                            }
                        }
                    }
                }
            }
        }

        return workbook;
    }

    /**
     * 获取cell的字符串，poi的getStringCellValue方法获取数字类型会抛出异常
     *
     * @param cell
     * @return
     */
    public static String getStringCellValue(Cell cell) {
        String value = "";
        if (cell != null) {
            switch (cell.getCellType()) {
                case HSSFCell.CELL_TYPE_NUMERIC:
                    HSSFDataFormatter dataFormatter = new HSSFDataFormatter();
                    value = dataFormatter.formatCellValue(cell);
                    break;
                default:
                    value = cell.getStringCellValue();
            }
        }

        if (null != value && !value.isEmpty()) {
            value = value.trim();
        }

        return value;
    }

    /**
     * 去除符号" "(非空格)和所有空格符
     * @param str
     * @return
     */
	/*
	public static String trim160AndWhitespace(String str) {
		if (!StringUtils.isEmpty(str)) {
			// 特殊字符
			char c = (char) 160;
			// 去掉头部的特殊字符
			do {
				str = StringUtils.trimLeadingCharacter(str, c);
				str = StringUtils.trimLeadingWhitespace(str);
			} while (str.length() != 0 && (str.charAt(0) == c || Character.isWhitespace(str.charAt(0))));

			// 去掉末尾的特殊字符
			do {
				str = StringUtils.trimTrailingCharacter(str, c);
				str = StringUtils.trimTrailingWhitespace(str);
			} while (str.length() != 0 && (str.charAt(str.length() - 1) == c || Character.isWhitespace(str.charAt(str.length() - 1))));
		}
		return str;
	}
	*/
}
